package com.yenlien.traceyun.plant.dao;

import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.plant.entity.ColdStorage;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface ColdStorageDao extends BaseJpaDao<ColdStorage> {

    List<ColdStorage> findByState(String state);

    ColdStorage findById(String id);

    @Query(value = "SELECT * FROM t_cold_storage WHERE company_id = ?1 AND rent_state = ?2 AND state = '1'", nativeQuery = true)
    List<ColdStorage> findByCompanyIdAndRentState(String companyId, String rentState);

    @Query(value = "SELECT * FROM t_cold_storage WHERE company_id = ?1  AND state = '1'", nativeQuery = true)
    List<ColdStorage> findByCompanyId(String companyId);

    @Query(value = "SELECT `name`, area, dev_id FROM t_cold_storage WHERE company_id = ?1 AND box_id IN (?2)  AND state = '1'", nativeQuery = true)
    List<JSONObject> getByCompIdAndBoxId(String companyId, List<String> boxIdList);



    List<ColdStorage> findByCompanyIdAndState(String companyId, String state);

    @Query(value = "SELECT area FROM t_cold_storage WHERE dev_id = ?1 AND box_id = ?2 AND state = '1'", nativeQuery = true)
    String getByDevIdAndBoxId(String devId, String boxId);

    @Query(value = "SELECT area, `name` coldName FROM t_cold_storage WHERE dev_id = ?1 AND box_id = ?2  AND state = '1'", nativeQuery = true)
    JSONObject getByDevIdBoxId(String devId, String boxId);

    @Query(value = "SELECT area FROM t_cold_storage WHERE box_id = ?1  AND state = '1'", nativeQuery = true)
    String getByDevIdAndBoxId(String boxId);

    @Query(value = "SELECT ANY_VALUE(box_id) boxId, ROUND(SUM(area), 2) sumArea, COUNT(id) num FROM t_cold_storage WHERE company_id = ?1 AND state = '1'  GROUP BY box_id", nativeQuery = true)
    List<JSONObject> getBAByNCompId(String compId);


    @Query(value = "SELECT COUNT(id) coldStorageNum, ROUND(SUM(area), 2) areaNum FROM t_cold_storage WHERE state = '1'", nativeQuery = true)
    JSONObject countInfo();

    @Query(value = "SELECT COUNT(id) coldStorageNum, ROUND(SUM(area), 2) areaNum FROM t_cold_storage WHERE company_id = ?1 AND state = '1'", nativeQuery = true)
    JSONObject countInfo(String compId);

    @Query(value = "SELECT `name` FROM t_cold_storage WHERE rent_user_id = ?1 AND company_id = ?2  AND state = '1'", nativeQuery = true)
    List<String> findNameByRentUserId(String rentUserId, String companyId);

    @Query(value = "SELECT * FROM t_cold_storage WHERE rent_user_id = ?1 AND company_id = ?2  AND state = '1'", nativeQuery = true)
    List<ColdStorage> findByRentUserId(String rentUserId, String companyId);


    @Query(value = "SELECT `name` AS name, dev_id FROM t_cold_storage WHERE company_id = ?1 AND box_id = ?2 ", nativeQuery = true)
    List<JSONObject> getByCompIdBoxId(String compId, String boxId);
}
